package com.zhangtai.modules.dao;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.zhangtai.modules.dto.ContractRecordDto;
import com.zhangtai.modules.dto.InsuranceChangeDto;
import com.zhangtai.modules.dto.InsuranceMemberRecordDto;
import com.zhangtai.modules.dto.UserBankCardExportDto;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface ExportDao extends BaseMapper {

    @Select("<script>" +
            "select a.*,@rank:=@rank + 1 AS no from (select name,case gender when '1' then '男' when '2' then '女' end gender,idcard,code,nation,nationality," +
            "nativeplace,mobile,regresidence,address,privatemail," +
            "case healthstatus when '1' then '非常好' when'2' then '良好' when'3' then '一般' when'4' then '有生理缺陷' when'5' then '残废' end healthstatus," +
            "case disbandment when '1' then '是' when '2' then '否' end disbandment,case marital when '1' then '已婚' when '2' then '未婚' end marital," +
            "beginworkdate,birthday,unit_id,org_id,job_id,job_level,hiredate,probationday,regular_day," +
            "case channel when '1' then '猎头' when '2' then '网站' when '3' then '内部推荐' when '4' then '外部推荐' when '5' then '现场招聘' " +
            "when '6' then '校园招聘' when '9' then '其他' end channel, case recruitment_web when '1' then '广西人才网' when '2' then '智联招聘' " +
            " when '3' then '58同城' when '4' then '前程无忧' when '5' then '猎聘网' when '99' then '其他' end recruitment_web ,inner_referrer," +
            "outer_referrer,work_address,case is_reinstate when '1' then '是' else '否' end is_reinstate from tb_employee_info i " +
            "<where> i.is_delete = 0 " +
            "<if test='code!=null'>and code=#{code} </if>" +
            "<if test='name!=null'>and name=#{name} </if>" +
            "<if test='orgname!=null'>and orgname=#{orgname} </if>" +
            "<if test='unitname!=null'>and unitname=#{unitname} </if>" +
            "<if test='jobname!=null'>and jobname=#{jobname} </if>" +
            "<if test='isReinstate!=null'>and is_reinstate=#{isReinstate} </if>" +
            "<if test='isEntryStatus!=null'>and is_entry_status =#{isEntryStatus} </if>" +
            "<if test='status!=null and status.size>0'>and  is_reinstate in " +
            " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'>and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "</where> ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getExportEmployeeInfo(@Param(value = "code") String code,
                                                   @Param(value = "name") String name,
                                                   @Param(value = "orgname") String orgname,
                                                   @Param(value = "unitname") String unitname,
                                                   @Param(value = "jobname") String jobname,
                                                   @Param(value = "status") List<Integer> status,
                                                   @Param(value="isReinstate") Integer isReinstate,
                                                    @Param(value = "orgIds") List<Long> orgIds,
                                                   @Param(value="isEntryStatus") Integer isEntryStatus);

    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "select i.name,e.school,e.speciality,schooldate,graduatedate,studystyle," +
            "case e.degree when '1' then '研究生' when '2' then '大学本科' when '3' then '大专' when '4' then '中专' when '5' then '技校' " +
            "when '6' then '高中' when '7' then '初中' when '8' then '小学' when '9' then '文盲' end degree," +
            "case istopgrade when '1' then '是' when '2' then '否' end istopgrade,grade_num,degree_num " +
            "from tb_employee_info i left join tb_employee_education e on i.oid = e.empoid " +
            "<where> i.is_delete = 0 " +
            "<if test='code!=null'>and code=#{code} </if>" +
            "<if test='name!=null'>and name=#{name} </if>" +
            "<if test='orgname!=null'>and orgname=#{orgname} </if>" +
            "<if test='unitname!=null'>and unitname=#{unitname} </if>" +
            "<if test='jobname!=null'>and jobname=#{jobname} </if>" +
            "<if test='isReinstate!=null'>and is_reinstate=#{isReinstate} </if>" +
            "<if test='isEntryStatus!=null'>and is_entry_status =#{isEntryStatus} </if>" +
            "<if test='status!=null and status.size>0'>and  is_reinstate in " +
            " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'>and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "</where> ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getExportEducation(@Param(value = "code") String code,
                                                @Param(value = "name") String name,
                                                @Param(value = "orgname") String orgname,
                                                @Param(value = "unitname") String unitname,
                                                @Param(value = "jobname") String jobname,
                                                @Param(value = "status") List<Integer> status,
                                                @Param(value="isReinstate") Integer isReinstate,
                                                @Param(value = "orgIds") List<Long> orgIds,
                                                @Param(value="isEntryStatus") Integer isEntryStatus);

    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "select i.name,case e.polity when '1' then '党员' when '2' then '预备党员' when '3' then '共青团员' when '4' then '群众' end polity," +
            "join_day,inorgname " +
            "from tb_employee_info i left join tb_employee_inorgname e on i.oid = e.empoid " +
            "<where> i.is_delete = 0 " +
            "<if test='code!=null'>and code=#{code} </if>" +
            "<if test='name!=null'>and name=#{name} </if>" +
            "<if test='orgname!=null'>and orgname=#{orgname} </if>" +
            "<if test='unitname!=null'>and unitname=#{unitname} </if>" +
            "<if test='jobname!=null'>and jobname=#{jobname} </if>" +
            "<if test='isReinstate!=null'>and is_reinstate=#{isReinstate} </if>" +
            "<if test='isEntryStatus!=null'>and is_entry_status =#{isEntryStatus} </if>" +
            "<if test='status!=null and status.size>0'>and  is_reinstate in " +
            " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'>and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "</where> ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getExportInorgname(@Param(value = "code") String code,
                                                @Param(value = "name") String name,
                                                @Param(value = "orgname") String orgname,
                                                @Param(value = "unitname") String unitname,
                                                @Param(value = "jobname") String jobname,
                                                @Param(value = "status") List<Integer> status,
                                                @Param(value="isReinstate") Integer isReinstate,
                                                @Param(value = "orgIds") List<Long> orgIds,
                                                @Param(value="isEntryStatus") Integer isEntryStatus);

    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "select i.name,company,department,begindate,enddate,responsibility,witness,witnesscontact " +
            "from tb_employee_info i left join tb_employess_societyexperience e on i.oid = e.empoid " +
            "<where> i.is_delete = 0 " +
            "<if test='code!=null'>and code=#{code} </if>" +
            "<if test='name!=null'>and name=#{name} </if>" +
            "<if test='orgname!=null'>and orgname=#{orgname} </if>" +
            "<if test='unitname!=null'>and unitname=#{unitname} </if>" +
            "<if test='jobname!=null'>and jobname=#{jobname} </if>" +
            "<if test='isReinstate!=null'>and is_reinstate=#{isReinstate} </if>" +
            "<if test='isEntryStatus!=null'>and is_entry_status =#{isEntryStatus} </if>" +
            "<if test='status!=null and status.size>0'>and  is_reinstate in " +
            " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'>and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "</where> ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getExportSociety(@Param(value = "code") String code,
                                                @Param(value = "name") String name,
                                                @Param(value = "orgname") String orgname,
                                                @Param(value = "unitname") String unitname,
                                                @Param(value = "jobname") String jobname,
                                                @Param(value = "status") List<Integer> status,
                                                @Param(value="isReinstate") Integer isReinstate,
                                                @Param(value = "orgIds") List<Long> orgIds,
                                              @Param(value="isEntryStatus") Integer isEntryStatus);

    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "select i.name,e.name familyName,case e.gender when '1' then '男' when '2' then '女' end gender,e.relation,e.company,e.phone," +
            "case is_emergency_contact when '0' then '不是' when '1' then '是' end  is_emergency_contact " +
            "from tb_employee_info i left join tb_family_member e on i.oid = e.empoid " +
            "<where> i.is_delete = 0 " +
            "<if test='code!=null'>and code=#{code} </if>" +
            "<if test='name!=null'>and name=#{name} </if>" +
            "<if test='orgname!=null'>and orgname=#{orgname} </if>" +
            "<if test='unitname!=null'>and unitname=#{unitname} </if>" +
            "<if test='jobname!=null'>and jobname=#{jobname} </if>" +
            "<if test='isReinstate!=null'>and is_reinstate=#{isReinstate} </if>" +
            "<if test='isEntryStatus!=null'>and is_entry_status =#{isEntryStatus} </if>" +
            "<if test='status!=null and status.size>0'>and  is_reinstate in " +
            " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'>and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "</where> ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getExportFamily(@Param(value = "code") String code,
                                              @Param(value = "name") String name,
                                              @Param(value = "orgname") String orgname,
                                              @Param(value = "unitname") String unitname,
                                              @Param(value = "jobname") String jobname,
                                              @Param(value = "status") List<Integer> status,
                                              @Param(value="isReinstate") Integer isReinstate,
                                              @Param(value = "orgIds") List<Long> orgIds,
                                             @Param(value="isEntryStatus") Integer isEntryStatus);

    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "select i.name,e.relative_name,e.relativeId_card,e.relationship,e.relative_unit "+
            "from tb_employee_info i left join tb_relation e on i.oid = e.oid " +
            "<where> i.is_delete = 0 " +
            "<if test='code!=null'>and code=#{code} </if>" +
            "<if test='name!=null'>and name=#{name} </if>" +
            "<if test='orgname!=null'>and orgname=#{orgname} </if>" +
            "<if test='unitname!=null'>and unitname=#{unitname} </if>" +
            "<if test='jobname!=null'>and jobname=#{jobname} </if>" +
            "<if test='isReinstate!=null'>and is_reinstate=#{isReinstate} </if>" +
            "<if test='isEntryStatus!=null'>and is_entry_status =#{isEntryStatus} </if>" +
            "<if test='status!=null and status.size>0'>and  is_reinstate in " +
            " <foreach item=\"s\" index=\"index\" collection=\"status\" open=\"(\" separator=\",\" close=\")\">#{s}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'>and org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "</where> ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getExportRelation(@Param(value = "code") String code,
                                             @Param(value = "name") String name,
                                             @Param(value = "orgname") String orgname,
                                             @Param(value = "unitname") String unitname,
                                             @Param(value = "jobname") String jobname,
                                             @Param(value = "status") List<Integer> status,
                                             @Param(value="isReinstate") Integer isReinstate,
                                             @Param(value = "orgIds") List<Long> orgIds,
                                               @Param(value="isEntryStatus") Integer isEntryStatus);


    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "Select r.*,i.name,i.jobname,i.job_id,i.orgname,i.org_id,i.job_level_id from tb_security_record r " +
//            "left join tb_employee_security e on e.oid = r.oid " +
            "left join tb_security s on s.security_id = r.security_id  " +

            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>  i.is_delete = 0 " +
            "<if test='time!=null'>and r.operate_time &lt;= #{time} </if>" +
            "<if test='type!=null'>and r.type = #{type} </if>" +
            "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +

            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +

            "<if test='recordIds!=null and recordIds.length>0'> and r.record_id in " +
            "<foreach item=\"id\" index=\"index\" collection=\"recordIds\" open=\"(\" separator=\",\" close=\")\">#{id}</foreach>" +
            "</if>"+
            "<if test='unitName!=null'>and r.unit_name = #{unitName} </if>" +
            "</where>  ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getSecurityRecord(@Param(value="time") String time,
                                               @Param(value = "type") Integer type,
                                               @Param(value = "isCheck") Integer isCheck,
                                               @Param(value = "isConfirm") Integer isConfirm,
                                               @Param(value = "unitName") String unitName,
                                               @Param(value = "recordIds") Long[] recordIds,
                                               @Param("contractIds")List<Long> contractIds);


    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "Select r.*,i.name,i.jobname,i.job_id,i.orgname,i.org_id,i.job_level_id from tb_security_record r " +
//            "left join tb_employee_security e on e.oid = r.oid " +
            "left join tb_security s on s.security_id = r.security_id  " +

            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>  i.is_delete = 0 and r.is_delete = 0 " +
            "<if test='time!=null'>and r.operate_time &lt;= #{time} </if>" +
            "<if test='type!=null'>and r.type = #{type} </if>" +
            "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +

            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +

//            "<if test='recordIds!=null and recordIds.length>0'> and r.record_id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"recordIds\" open=\"(\" separator=\",\" close=\")\">#{id}</foreach>" +
//            "</if>"+
            "<if test='unitName!=null'>and r.unit_name = #{unitName} </if>" +
            "</where>  ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>>  getSecurityRecordAll(@Param(value="time") String time,
                                               @Param(value = "type") Integer type,
                                               @Param(value = "isCheck") Integer isCheck,
                                               @Param(value = "isConfirm") Integer isConfirm,
                                               @Param(value = "unitName") String unitName,
//                                               @Param(value = "recordIds") Long[] recordIds,
                                               @Param("contractIds")List<Long> contractIds);

    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "Select r.*,i.name,i.job_level,i.job_level_id,i.jobname,i.job_id,i.orgname,i.org_id from tb_funds_record r " +
//            "left join tb_employee_security e on e.oid = r.oid " +
            "left join tb_security s on s.security_id = r.security_id  " +
            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>  i.is_delete = 0 " +
            "<if test='time!=null'>and r.operate_time &lt;= #{time} </if>" +
            "<if test='type!=null'>and r.type = #{type} </if>" +
            "<if test='unitName!=null'>and r.unit_name = #{unitName} </if>" +
            "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +

            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +

            "<if test='recordIds!=null and recordIds.length>0'> and r.record_id in " +
            "<foreach item=\"id\" index=\"index\" collection=\"recordIds\" open=\"(\" separator=\",\" close=\")\">#{id}</foreach>" +
            "</if>"+
            "</where>  ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getFundsRecord(@Param(value="time") String time,
                                            @Param(value = "type") Integer type,
                                            @Param(value = "isCheck") Integer isCheck,
                                            @Param(value = "isConfirm") Integer isConfirm,
                                            @Param(value = "unitName") String unitName,
                                            @Param(value = "recordIds") Long[] recordIds
            ,@Param("contractIds")List<Long> contractIds);




    @Select("<script>select a.*,@rank:=@rank + 1 AS no from (" +
            "Select r.*,i.name,i.job_level,i.job_level_id,i.jobname,i.job_id,i.orgname,i.org_id from tb_funds_record r " +
//            "left join tb_employee_security e on e.oid = r.oid " +
            "left join tb_security s on s.security_id = r.security_id  " +
            "left join tb_employee_info i on i.oid = r.oid " +
            "<where>  i.is_delete = 0 and r.is_delete = 0 " +
            "<if test='time!=null'>and r.operate_time &lt;= #{time} </if>" +
            "<if test='type!=null'>and r.type = #{type} </if>" +
            "<if test='unitName!=null'>and r.unit_name = #{unitName} </if>" +
            "<if test='isCheck!=null'>and r.is_check = #{isCheck} </if>" +
            "<if test='isConfirm!=null'>and r.is_confirm = #{isConfirm} </if>" +

            "<if test='contractIds!=null and contractIds.size>0'> and s.contract_main_id in " +
            " <foreach item=\"contractId\" index=\"index\" collection=\"contractIds\" open=\"(\" separator=\",\" close=\")\">#{contractId}</foreach>" +
            "</if>"+
            "<if test='contractIds==null or contractIds.size==0'> and s.contract_main_id = null </if>" +

//            "<if test='recordIds!=null and recordIds.length>0'> and r.record_id in " +
//            "<foreach item=\"id\" index=\"index\" collection=\"recordIds\" open=\"(\" separator=\",\" close=\")\">#{id}</foreach>" +
//            "</if>"+
            "</where>  ) a,(select @rank:=0)b" +
            "</script>")
    List<Map<String,Object>> getFundsRecordAll(@Param(value="time") String time,
                                            @Param(value = "type") Integer type,
                                            @Param(value = "isCheck") Integer isCheck,
                                            @Param(value = "isConfirm") Integer isConfirm,
                                            @Param(value = "unitName") String unitName
//                                            @Param(value = "recordIds") Long[] recordIds
            ,@Param("contractIds")List<Long> contractIds);



    @Select("<script>SELECT	UBC.*,EL.CODE,EL.NAME,EL.org_id,EL.orgname,	EL.unit_id,	EL.unitname,"+
            "EL.job_id,	EL.jobname,	EL.job_Level_id,EL.job_level,EL.job_level_type "+
            "FROM   tb_user_bank_card UBC "+
            " LEFT JOIN tb_employee_info EL ON EL.oid = UBC.oid " +
            "<where> UBC.is_delete = 0 " +

            "<if test='orgIds!=null and orgIds.size>0'> and UBC.unit_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "<if test='orgIds==null or orgIds.size==0'> and UBC.unit_id = null </if>" +

            "<if test='name!=null'> and EL.name like '%${name}%'</if>" +
            "<if test='code!=null'> and  EL.code like '%${code}%'  </if>" +
            "<if test='orgname!=null'> and EL.orgname like '%${orgname}%'  </if>" +
            "<if test='unitname!=null'> and  EL.unitname like '%${unitname}%'  </if>" +
            "<if test='jobname!=null'> and EL.jobname like '%${jobname}%'  </if>" +
            "<if test='status!=null'> and UBC.status = #{status}  </if>" +
            "<if test='status==null'> and (UBC.status = '2' or UBC.status = '3' ) </if>" +
            "</where>  ORDER BY UBC.update_time DESC" +
            "</script>")
    public List<UserBankCardExportDto> getUserBankCardPage(@Param("orgIds") List<Long> orgIds,
                                                           @Param("name") String name,
                                                           @Param("code") String code,
                                                           @Param("orgname") String orgname,
                                                           @Param("unitname") String unitname,
                                                           @Param("jobname") String jobname,
                                                           @Param("status") Integer status);


    @Select("<script>SELECT t1.id, t4.code,t4.name,t4.gender,t4.idcard,t4.hiredate,t4.org_id,t4.unit_id,t4.job_id," +
            "t3.operate_type,t3.org_name begin_org_name,t3.unit_name begin_unit_name,t3.job_name begin_job_name," +
            "t3.contract_type begin_contract_type,t3.contract_begin_date,t3.contract_end_date,t3.employer_company," +
            "t3.contract_time_type,t3.contract_oss_url begin_contract_url,t2.sign_date end_sign_date," +
            "t2.contract_oss_url end_contract_url,t1.status,t1.end_time,t1.end_type,t4.hiredate,t4.employeestatus " +
            "FROM tb_contract_record t1 " +
            "LEFT JOIN tb_contract_manger t2 ON t1.end_contract_id = t2.id " +
            "LEFT JOIN tb_contract_manger t3 ON t3.id = t1.start_contract_id " +
            "LEFT JOIN tb_employee_info t4 on t4.oid = t1.oid" +
            "<where> t1.is_delete = 0 " +
            "<if test='code!=null and code !=&apos;&apos;'> and t4.code like '%${code}%'</if>" +
            "<if test='name!=null and name !=&apos;&apos;'> and t4.name like '%${name}%'</if>" +
            "<if test='contractType!=null'> and t3.contract_type = #{contractType}</if>" +
            "<if test='contractTimeType!=null'> and t3.contract_time_type = #{contractTimeType}</if>" +
            "<if test='contractEndDate!=null'> and t3.contract_end_date &lt;= '${contractEndDate} 23:59:59'</if>" +
            "<if test='status!=null'> and t1.status = #{status}</if>" +
            "<if test='orgId!=null and orgId.size>0'> and t4.org_id in " +
            "<foreach item=\"org\" index=\"index\" collection=\"orgId\" open=\"(\" separator=\",\" close=\")\">#{org}</foreach>" +
            "</if>"+
            "<if test='unitId!=null and unitId.size>0'> and t4.unit_id in " +
            "<foreach item=\"unit\" index=\"index\" collection=\"unitId\" open=\"(\" separator=\",\" close=\")\">#{unit}</foreach>" +
            "</if>"+
            "<if test='orgIds!=null and orgIds.size>0'> and t1.unit_id in " +
            "<foreach item=\"ids\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{ids}</foreach>" +
            "</if>" +
            "<if test='orgIds.size==0 or orgIds==null'> and t1.unit_id = null </if>"+
            "</where>" +
            " order by t1.create_time desc" +
            "</script>")
    List<ContractRecordDto> getRecordList(@Param("code")String code,
                                          @Param("name")String name,
                                          @Param("orgId")List<Long> orgId,
                                          @Param("unitId")List<Long> unitId,
                                          @Param("contractType")Integer contractType,
                                          @Param("contractTimeType")Integer contractTimeType,
                                          @Param("status")Integer status,
                                          @Param("contractEndDate")String contractEndDate,
                                          @Param("orgIds")List<Long> orgIds);


    @Select("<script>select t1.id,t1.oid,t1.insurance_id,t2.code,t2.name,t2.gender,t2.idcard,t2.job_type,t2.employeestatus,t2.job_level_id," +
            "t2.hiredate,t2.org_id,t2.unit_id,t2.job_id,t1.status,t3.company_name,t1.operate_name," +
            "t1.operater_code,t1.operate_time, case t4.security_id when t4.security_id  then 1 else 0 end type from tb_employee_insurance t1 " +
            "left join tb_employee_info t2 on t1.oid = t2.oid " +
            "left join tb_insurance t3 on t1.insurance_id = t3.id " +
            "left join tb_employee_security t4 on t1.oid = t4.oid " +
            "<where> t1.is_delete = 0 " +
            "<if test='orgIds!=null and orgIds.size>0'> and t1.unit_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "<if test='orgIds==null or orgIds.size==0'> and t1.unit_id = null  </if></where>" +
            "order by t1.operate_time desc </script>")
    List<InsuranceMemberRecordDto> getMemberRecordList(@Param("orgIds") List<Long> orgIds);

    @Select("<script>select t1.operation_time,t1.operate_name,t1.operater_code,t2.code,t2.name,t2.gender,t2.job_type,t2.employeestatus,t2.job_level_id," +
            "t2.hiredate,t2.job_id,t2.org_id,t2.unit_id,t1.type,case t4.security_id when t4.security_id  then 1 else 0 end security_type,t2.idcard,t3.company_name,t1.status " +
            " from ( select oid,type,count(oid) count,insurance_id,max(operation_time) operation_time,operate_name,operater_code,unit_id,org_id,status from tb_insurance_change " +
            "where  (type =1 or type =0) and is_delete = 0 " +
            "<if test='startTime !=null and startTime !=&apos;&apos;'>and operation_time &gt;= '${startTime} 00:00:00'</if> " +
            "<if test='endTime !=null and endTime !=&apos;&apos;'>and operation_time &lt;= '${endTime} 23:59:59' </if> " +
            "<if test='operateName !=null and operateName!=&apos;&apos;'> and operate_name like '%${operateName}%'</if>"+
            "group by oid,insurance_id,unit_id ) t1" +
            " left join tb_employee_info t2 on t1.oid = t2.oid " +
            " left join tb_insurance t3 on t3.id = t1.insurance_id " +
            " left join tb_employee_security t4 on t1.oid = t4.oid " +
            "<where> t1.count%2>0 " +
            "<if test='orgIds!=null and orgIds.size>0'> and  t1.unit_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgIds\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>"+
            "<if test='orgIds==null or orgIds.size==0'> and t1.unit_id = null  </if> " +
            "<if test='orgId!=null and orgId.size>0'> and  t1.org_id in " +
            " <foreach item=\"orgId\" index=\"index\" collection=\"orgId\" open=\"(\" separator=\",\" close=\")\">#{orgId}</foreach>" +
            "</if>" +
            "<if test='orgId!=null and orgId.size ==0'> and t1.org_id = null</if>" +
            "<if test='companyName !=null and companyName!=&apos;&apos;'> and t3.companyName like '%${companyName}%' </if>"+
            "<if test='employeestatus !=null and employeestatus!=&apos;&apos;'> and t2.employeestatus = #{employeestatus}</if>"+
            "<if test='status !=null and status == 0'> and t4.security_id is null </if>"+
            "<if test='type !=null '> and t1.type = #{type}</if>"+
            "<if test='status !=null and status == 1'> and t4.security_id is not null </if>"+
            "</where>"+
            " order by t1.operation_time desc </script>" )
    List<InsuranceChangeDto> getMemberChangeList(@Param("orgIds")List<Long> orgIds,@Param("startTime")String startTime,@Param("endTime")String endTime,
                                                 @Param("companyName")String companyName,@Param("employeestatus")String employeestatus,@Param("type") Integer type,
                                                 @Param("status")Integer status,@Param("operateName")String operateName, @Param("orgId")List<Long> orgId);
}
